<?php
namespace globalunit\utils;
use QYS\Db\Mysql;
use globalunit\utils\Utils;
use QYS\Log\Log;
class DBHelper
{
    /**
     * @param $tblname 数据库名字
     * @param $where  查询条件
     * @param $orderBy
     * @param $groupBy
     * @param $limit
     * @param $page
     * @param $field
     * @return string sql语句
     */
    static public function querysql($tblname, $where = "", $orderBy = "", $groupBy = "", $limit = 0, $page = 1, $field = "*") {
        $sql = "select $field from $tblname";
        if(!empty($where)){
            $sql .= " where {$where}";
        }
        if(!empty($groupBy)){
            $sql .= " group by `$groupBy`";
        }
        if(!empty($orderBy)){
            $sql .= " order by {$orderBy}";
        }
        if(!empty($limit)){
            $start = ($page - 1) * $limit; //从哪里开始
            $sql .= " limit {$start}, {$limit}";
        }
        return $sql;
    }

    /**
     * @param $tblname 数据库名字
     * @param $a      数据字典
     * @return string sql语句
     */
    static public function insertsql($tblname,$a) {
        $values=array();
        $columns=array();

        $values=array();
        $columns=array();
        foreach($a as $k=>$v){
            array_push($columns,"`$k`");
            if(gettype($v)=='integer'){
                array_push($values,"$v");
            }elseif (gettype($v)=='float'){
                array_push($values,"'$v'");
            }elseif (gettype($v)=='double'){
                array_push($values,"'$v'");
            }elseif (gettype($v)=='string'){
                array_push($values,"'$v'");
            }elseif (gettype($v)=='array'){
                $s =json_encode($v,true);
                array_push($values,"'$s'");
            }elseif (gettype($v)=='NULL'){
            }elseif (gettype($v)==''){
                array_push($values,"''");
            }else{
                $clazzname = get_class($v);
                if($clazzname=='DateTime'){
                    $a=$v->format('Y-m-d H:i:s');
                    array_push($values,"'$a'");
                }else{
                    array_push($values,"'$v'");
                }
            }

        }
        $values=join(",",$values);
        $columns=join(",",$columns);

        $sql = " INSERT INTO $tblname ($columns)  VALUES($values) ";
        return $sql;
    }

    static public function array2in($a)
    {
        $b = array();
        foreach($a as $k=>$v){
            array_push($b,"'$v'");
        }
        $c = join(",",$b);
        return $c;
    }

    /**
     * @param $tblname  数据库名字
     * @param $obj     数据字典
     * @param $conds   更新条件
     * @return string  sql语句
     */
    static public function udpatesql($tblname,$obj,$conds) {
        $values = array();
        if(is_array($obj)){
            foreach($obj as $k=>$v) {
                if(gettype($v)=='integer'){
                    array_push($values," `$k`=$v ");
                }elseif (gettype($v)=='double'){
                    array_push($values," `$k`='$v' ");
                }elseif (gettype($v)=='float'){
                    array_push($values," `$k`='$v' ");
                }elseif (gettype($v)=='string'){
                    array_push($values," `$k`='$v' ");
                }elseif (gettype($v)=='array'){
                    $s=json_encode($v);
                    array_push($values," `$k`='$s' ");
                }elseif (gettype($v) == 'NULL') {
                }else{
                    $clazzname = get_class($v);
                    if($clazzname=='DateTime'){
                        $a=$v->format('Y-m-d H:i:s');
                        array_push($values," `$k`='$a' ");
                    }else{
                        array_push($values," `$k`='$v' ");
                    }
                }
            }
            $x = join(",",$values);
        }else{
            $x = $obj;
        }
        $sql = "  UPDATE $tblname set $x where $conds ";
        return $sql;
    }

    static public function upsertsql($tblname,$obj,$conds){
        $values=array();
        $columns=array();
        foreach($obj as $k=>$v){
            array_push($columns,"`$k`");
            if(gettype($v)=='integer'){
                array_push($values,"$v");
            }elseif (gettype($v)=='float'){
                array_push($values,"'$v'");
            }elseif (gettype($v)=='double'){
                array_push($values,"'$v'");
            }elseif (gettype($v)=='string'){
                array_push($values,"'$v'");
            }elseif (gettype($v)=='array'){
                $s =json_encode($v,true);
                array_push($values,"'$s'");
            }elseif (gettype($v)==''){
                array_push($values,"''");
            }else{
                $clazzname = get_class($v);
                if($clazzname=='DateTime'){
                    $a=$v->format('Y-m-d H:i:s');
                    array_push($values,"'$a'");
                }else{
                    array_push($values,"'$v'");
                }
            }

        }
        $values=join(",",$values);
        $columns=join(",",$columns);
        $sql = "INSERT INTO $tblname ($columns)  VALUES($values)  ON DUPLICATE KEY UPDATE $conds ";
        return $sql;
    }

    static public function query($sql)
    {
        Utils::dump($sql);
        $db = Mysql::getInstance("mysql1");
        $result = $db->query($sql);
        $a=array();
        if($result===false){
            return $a;
        }

        while($row = $result->fetch_assoc())
        {
            array_push($a,$row);
        }
        return $a;
    }

    static public function fetchone($sql)
    {
        Utils::dump($sql);
        $db = Mysql::getInstance("mysql1");
        $result = $db->query($sql);
        $a=array();
        if($result===false){
            return $a;
        }
        return $result->fetch_assoc();
    }

    static public function queryall($tblname,$conds)
    {
        $sql = self::querysql($tblname,$conds);
        Utils::dump($sql);
        $db = Mysql::getInstance("mysql1");
        $result = $db->query($sql);
        $a=array();
        if($result===false){
            return $a;
        }

        while($row = $result->fetch_assoc())
        {
            array_push($a,$row);
        }
        return $a;
    }

    static public function query_update($sql)
    {
        Utils::dump($sql);
        $db = Mysql::getInstance("mysql1");
        $result = $db->query($sql);
        $a=array();
        if($result===false){
            return $a;
        }
        return 1;
    }
}
